package mz.co.frameworks.loader;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.Iterator;

import mz.co.framework.error.ISystemException;
import mz.co.framework.error.SystemException;

import org.apache.poi.ss.usermodel.Sheet;

/**
 * Excel Loader (POI), responsible to transform all content from a Excel File to
 * DataSet (default format).
 * 
 * @author Tiago da Fonseca Frazao
 */
public abstract class ExcelLoader implements ILoader {

	protected abstract org.apache.poi.ss.usermodel.Workbook getWorkbook(
			InputStream is) throws IOException;

	@Override
	public Workbook loadWorkbook(String pathName) throws SystemException {

		return loadWorkbook(new File(pathName));
	}

	@Override
	public Workbook loadWorkbook(File file) throws SystemException {

		try {

			if (file.isDirectory()) {
				throw new SystemException(
						ISystemException.NOT_FILE_LOADER_ERROR);
			}

			FileInputStream fis = new FileInputStream(file);

			Workbook workbook = loadWorkbook(fis);

			fis.close();

			return workbook;

		} catch (FileNotFoundException fe) {
			throw new SystemException(ISystemException.LOADER_ERROR, fe);
		} catch (IOException io) {
			throw new SystemException(ISystemException.LOADER_ERROR, io);
		}

	}

	@Override
	public Workbook loadWorkbook(InputStream is) throws SystemException {

		Workbook workbook = new Workbook();

		try {
			//
			// Create an excel workbook from the file system.
			//
			org.apache.poi.ss.usermodel.Workbook wb = getWorkbook(is);

			//
			// Get all sheets on the workbook.
			//
			for (int i = 0; i < wb.getNumberOfSheets(); i++) {
				workbook.addDataSet(loadSheet(wb.getSheetAt(i)));
			}

		} catch (IOException ie) {
			throw new SystemException(ISystemException.LOADER_ERROR, ie);
		}

		return workbook;

	}

	@SuppressWarnings("rawtypes")
	private DataSet loadSheet(Sheet sheet) throws SystemException {
		DataSet dataSet = new DataSet();
		dataSet.setCode(sheet.getSheetName());

		//
		// When we have a sheet object in hand we can iterator on
		// each sheet's rows and on each row's cells.
		//

		Iterator rows = sheet.rowIterator();

		while (rows.hasNext()) {

			org.apache.poi.ss.usermodel.Row row = (org.apache.poi.ss.usermodel.Row) rows
					.next();

			Row nrow = new Row("R" + row.getRowNum());

			Iterator cells = row.cellIterator();

			while (cells.hasNext()) {
				org.apache.poi.ss.usermodel.Cell cell = (org.apache.poi.ss.usermodel.Cell) cells
						.next();

				Cell ncell = new Cell("C" + cell.getColumnIndex() + "R"
						+ row.getRowNum(), getValue(cell));

				nrow.addCell(ncell);
			}

			dataSet.addRow(nrow);
		}

		return dataSet;
	}

	private String getValue(org.apache.poi.ss.usermodel.Cell cell) {

		switch (cell.getCellType()) {
		case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC:
			return String.valueOf(cell.getNumericCellValue());
		case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_BOOLEAN:
			return String.valueOf(cell.getBooleanCellValue());
		case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_FORMULA:
			return cell.getCellFormula();
		case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_BLANK:
			return "";
		default:
			return cell.getStringCellValue();

		}

	}
}
